package com.itcast.mapper;

import com.github.pagehelper.Page;
import com.itcast.pojo.CountData;
import com.itcast.pojo.Student;
import com.itcast.pojo.StudentDegreeData;
import com.itcast.pojo.StudentQueryParam;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

@Mapper
public interface StudentMapper {
    /**
     * 学员列表数据的条件分页查询
     * @param param
     * @return
     */
    Page<Student> listQuery(StudentQueryParam param);

    /**
     * 批量删除学员信息
     * @param ids
     * @return
     */
    void delete(@Param("ids") List<Integer> ids);

    /**
     * 添加学员信息
     * @param student
     * @return
     */
    void add(Student student);

    /**
     * 根据主键ID查询学员的信息
     * @param id
     * @return
     */
    @Select("select id, name, no, gender, phone, id_card, is_college, address, degree, graduation_date, clazz_id, violation_count, violation_score, create_time, update_time from student where id = #{id};")
    Student getById(Integer id);

    /**
     * 修改学员的数据信息
     * @param student
     * @return
     */
    void update(Student student);

    /**
     * 修改学员的违纪信息
     * @param id
     * @param score
     * @return
     */
    @Update("update student set violation_count = violation_count + 1,\n" +
            "                   violation_score = violation_score + #{score},\n" +
            "                   update_time = now()\n" +
            "where id = #{id};")
    void violationUpdate(@Param("id") Integer id, @Param("score") Integer score);

    /**
     * 统计学员的学历信息
     * @return
     */
    @Select("select (case degree when 1 then '初中'\n" +
            "                    when 2 then '高中'\n" +
            "                    when 3 then '大专'\n" +
            "                    when 4 then '本科'\n" +
            "                    when 5 then '硕士'\n" +
            "                    else '博士'\n" +
            "    end) name,count(0) value\n" +
            "from student group by degree\n" +
            "order by value;")
    List<StudentDegreeData> studentDegreeData();

    /**
     * 统计每一个班级的人数
     * @return
     */
    @Select("select clazz.name clazzName,count(*) total\n" +
            "from student left join clazz on student.clazz_id = clazz.id\n" +
            "group by student.clazz_id\n" +
            "order by total")
//    @Select("select clazz.name clazzName,count(student.clazz_id) total\n" +
//            "from student right join clazz on student.clazz_id = clazz.id\n" +
//            "group by clazzName\n" +
//            "order by total;")
    List<CountData> studentCountData();

}
